-- =========================================================================
-- Title: Create Rules tables
-- Author: Nem W. Schlecht
-- Create Date: 2013-07-01
-- License: Creative Commons Attribution-Share Alike
-- Description: Setup rule checking and fixing tables for SQL Auditng
--     system
-- =========================================================================
SET NOEXEC OFF;

USE ServerAudit;
GO

IF (
		@@SERVERNAME <> 'corpi-14967\dba' -- EDIT THIS
		OR DB_NAME() <> 'ServerAudit'
		)
BEGIN
	PRINT 'You''re on the wrong server.';

	SET NOEXEC ON;
END;

SET NOCOUNT ON;

IF OBJECT_ID('dbo.auditRules', 'u') IS NOT NULL
BEGIN
	DROP TABLE [auditRules];
	DROP TABLE [auditRuleIncludes];
	DROP TABLE [auditRuleExcludes];
	DROP TABLE [auditRuleFix];
	DROP TABLE [auditCompareRules];
END;
GO

DECLARE @lastRuleId INT;

CREATE TABLE [auditRules] (
	ruleId INT IDENTITY PRIMARY KEY
	, ruleName VARCHAR(100) NOT NULL
	, runOrder INT NOT NULL
	, [action] VARCHAR(100) NOT NULL
	, configKey VARCHAR(100) NOT NULL
	, [target] VARCHAR(100) NOT NULL
	, createdOn DATETIME NOT NULL
	, createdBy VARCHAR(200) NOT NULL
	, modifiedOn DATETIME NOT NULL
	, modifiedBy VARCHAR(200) NOT NULL
	, notes TEXT NULL
	)
;

ALTER TABLE auditRules ADD CONSTRAINT [ar_CO_def] DEFAULT(GETDATE())
FOR [createdOn];

ALTER TABLE auditRules ADD CONSTRAINT [ar_CB_def] DEFAULT(SUSER_NAME())
FOR [createdBy];

ALTER TABLE auditRules ADD CONSTRAINT [ar_MO_def] DEFAULT(GETDATE())
FOR [modifiedOn];

ALTER TABLE auditRules ADD CONSTRAINT [ar_MB_def] DEFAULT(SUSER_NAME())
FOR [modifiedBy];

ALTER TABLE auditRules ADD CONSTRAINT [ar_Notes_def] DEFAULT(NULL)
FOR [notes];

CREATE TABLE auditRuleIncludes (
	incId INT IDENTITY PRIMARY KEY
	, ruleId INT NOT NULL
	, systemInclude VARCHAR(200) NOT NULL
	)
;

CREATE TABLE auditRuleExcludes (
	excId INT IDENTITY PRIMARY KEY
	, ruleId INT NOT NULL
	, systemExclude VARCHAR(200) NOT NULL
	)
;

CREATE TABLE auditRuleFix (
	ruleFixId INT IDENTITY PRIMARY KEY
	, ruleId INT NOT NULL
	, ruleSQL VARCHAR(MAX) NOT NULL
	)
;

----------------------------------------------------------------------
/*
-- The following are just examples

INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'sa-bob.smith'
	, 100
	, 'contains'
	, 'ServerRole:sysadmin'
	, 'DOMAIN\bob.smith'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
CREATE LOGIN [DOMAIN\bob.smith] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
' + 'GO
EXEC master..sp_addsrvrolemember
	@loginame = N''DOMAIN\bob.smith''
	, @rolename = N''sysadmin''
' + 'GO
'
	)
;

----------------------------------------------------------------------

INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'sa-past.user'
	, 1100
	, 'notcontains'
	, 'ServerRole:sysadmin'
	, 'DOMAIN\past.user'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC master..sp_dropsrvrolemember
	@loginame = N''DOMAIN\past.user''
	, @rolename = N''sysadmin''
' + 'GO
'
	)
;

----------------------------------------------------------------------

INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'sa-ops.user'
	, 1100
	, 'notcontains'
	, 'ServerRole:sysadmin'
	, 'DOMAIN\ops.user'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleExcludes (
	ruleId
	, systemExclude
	)
VALUES (
	@lastRuleId
	, 'server1'
	)
	, (
	@lastRuleId
	, 'server2'
	)
	, (
	@lastRuleId
	, 'server3'
	)
;

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC master..sp_dropsrvrolemember
	@loginame = N''DOMAIN\ops.user''
	, @rolename = N''sysadmin''
' + 'GO
'
	)
;

*/
----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-ola-output-cleanup'
	, 2000
	, 'equals'
	, 'AgentJob:Output File Cleanup'
	, 'enabled:scheduled'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [msdb]
' + 'GO
PRINT ''**** Please install and configure the Ola Hallengren Maintenance script.'';
PRINT ''**** Output File Cleanup might be missing.'';

DECLARE @schedule_id INT;
EXEC msdb.dbo.sp_add_jobschedule
	@job_name = N''Output File Cleanup''
	, @name = N''Daily 0200''
	, @enabled = 1
	, @freq_type = 4
	, @freq_interval = 1
	, @freq_subday_type = 1
	, @freq_subday_interval = 0
	, @freq_relative_interval = 0
	, @freq_recurrence_factor = 1
	, @active_start_date = 20000101
	, @active_end_date = 99991231
	, @active_start_time = 20000
	, @active_end_time = 235959
	, @schedule_id = @schedule_id OUTPUT
;
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-ola-integrity-sys'
	, 2100
	, 'equals'
	, 'AgentJob:DatabaseIntegrityCheck - SYSTEM_DATABASES'
	, 'enabled:scheduled'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [msdb]
' + 'GO
PRINT ''**** Please install and configure the Ola Hallengren Maintenance script.'';
PRINT ''**** DatabaseIntegrityCheck for SYSTEM_DATABASES might be missing.'';

DECLARE @schedule_id INT;
EXEC msdb.dbo.sp_add_jobschedule
	@job_name = N''DatabaseIntegrityCheck - SYSTEM_DATABASES''
	, @name = N''Daily 2300''
	, @enabled = 1
	, @freq_type = 4
	, @freq_interval = 1
	, @freq_subday_type = 1
	, @freq_subday_interval = 0
	, @freq_relative_interval = 0
	, @freq_recurrence_factor = 1
	, @active_start_date = 20000101
	, @active_end_date = 99991231
	, @active_start_time = 230000
	, @active_end_time = 235959
	, @schedule_id = @schedule_id OUTPUT
;
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-ola-integrity-user'
	, 2100
	, 'contains'
	, 'AgentJob:DatabaseIntegrityCheck - USER_DATABASES%'
	, 'enabled:scheduled'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleIncludes (
	ruleId
	, systemInclude
	)
VALUES (
	@lastRuleId
	, 'server1'
	)
;

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, '
PRINT ''**** Please install and configure the Ola Hallengren Maintenance script.'';
PRINT ''**** DatabaseIntegrityCheck for USER_DATABASES missing.'';
	'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-ola-indexoptimize'
	, 2100
	, 'contains'
	, 'AgentJob:IndexOptimize - %'
	, 'enabled:scheduled'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, '
PRINT ''**** Please install and configure the Ola Hallengren Maintenance script.'';
PRINT ''**** IndexOptimize missing.'';
	'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-reboot-notification'
	, 2000
	, 'equals'
	, 'AgentJob:Failover/Reboot Notification'
	, 'enabled:scheduled'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [msdb]
;
' + 'GO

DECLARE @ReturnCode INT;
SET @ReturnCode = 0;
DECLARE @jobId BINARY (16);

EXEC @ReturnCode 
	= msdb.dbo.sp_add_job
	@job_name = N''Failover/Reboot Notification''
	, @enabled = 1
	, @notify_level_eventlog = 0
	, @notify_level_email = 2
	, @notify_level_netsend = 0
	, @notify_level_page = 0
	, @delete_level = 0
	, @description = N''No description available.''
	, @category_name = N''Server Maintenance''
	, @owner_login_name = N''sa''
	, @notify_email_operator_name = N''DBA''
	, @job_id = @jobId OUTPUT
;

EXEC @ReturnCode 
	= msdb.dbo.sp_add_jobstep
	@job_id = @jobId
	, @step_name = N''Restart Notificiaton started''
	, @step_id = 1
	, @cmdexec_success_code = 0
	, @on_success_action = 1
	, @on_success_step_id = 0
	, @on_fail_action = 2
	, @on_fail_step_id = 0
	, @retry_attempts = 0
	, @retry_interval = 0
	, @os_run_priority = 0
	, @subsystem = N''TSQL''
	, @command =
	N''SET NOCOUNT ON;
DECLARE @en_subject VARCHAR(100);
SET @en_subject = @@SERVERNAME + '''' started'''';
EXEC msdb.dbo.sp_send_dbmail
		@recipients=N''''dba@your-domain.com''''
		, @body=''''Message Body''''
		, @subject=@en_subject
		, @profile_name=''''DBA_Profile''''
		, @query =''''SELECT @@SERVERNAME AS [Server Name], GETDATE() [Start/Failover Date]''''
	''
	, @database_name = N''master''
	, @flags = 0
;

EXEC @ReturnCode 
	= msdb.dbo.sp_update_job
	@job_id = @jobId
	, @start_step_id = 1
;

EXEC @ReturnCode 
	= msdb.dbo.sp_add_jobschedule
	@job_id = @jobId
	, @name = N''Failover''
	, @enabled = 1
	, @freq_type = 64
	, @freq_interval = 0
	, @freq_subday_type = 0
	, @freq_subday_interval = 0
	, @freq_relative_interval = 0
	, @freq_recurrence_factor = 0
	, @active_start_date = 20120417
	, @active_end_date = 99991231
	, @active_start_time = 0
	, @active_end_time = 235959
;

EXEC @ReturnCode 
	= msdb.dbo.sp_add_jobserver
	@job_id = @jobId
	, @server_name = N''(local)''
;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-error-log-rotation'
	, 2000
	, 'equals'
	, 'AgentJob:Rotate Error Log'
	, 'enabled:scheduled'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [msdb]
;
' + 'GO

DECLARE @ReturnCode INT;
SET @ReturnCode = 0;
DECLARE @jobId BINARY (16);

EXEC @ReturnCode 
	= msdb.dbo.sp_add_job
	@job_name = N''Rotate Error Log''
	, @enabled = 1
	, @notify_level_eventlog = 0
	, @notify_level_email = 2
	, @notify_level_netsend = 0
	, @notify_level_page = 0
	, @delete_level = 0
	, @description = N''Weekly Rotate Error Log''
	, @category_name = N''Server Maintenance''
	, @owner_login_name = N''sa''
	, @notify_email_operator_name = N''DBA''
	, @job_id = @jobId OUTPUT
;

EXEC @ReturnCode 
	= msdb.dbo.sp_add_jobstep
	@job_id = @jobId
	, @step_name = N''Cycle Log''
	, @step_id = 1
	, @cmdexec_success_code = 0
	, @on_success_action = 1
	, @on_success_step_id = 0
	, @on_fail_action = 2
	, @on_fail_step_id = 0
	, @retry_attempts = 0
	, @retry_interval = 0
	, @os_run_priority = 0
	, @subsystem = N''TSQL''
	, @command = N''EXEC sp_cycle_errorlog''
	, @database_name = N''master''
	, @flags = 0
;

EXEC @ReturnCode 
	= msdb.dbo.sp_update_job
	@job_id = @jobId
	, @start_step_id = 1
;

EXEC @ReturnCode 
	= msdb.dbo.sp_add_jobschedule
	@job_id = @jobId
	, @name = N''Sunday AM weekly''
	, @enabled = 1
	, @freq_type = 8
	, @freq_interval = 1
	, @freq_subday_type = 1
	, @freq_subday_interval = 0
	, @freq_relative_interval = 0
	, @freq_recurrence_factor = 1
	, @active_start_date = 20121206
	, @active_end_date = 99991231
	, @active_start_time = 1
	, @active_end_time = 235959
;

EXEC @ReturnCode 
	= msdb.dbo.sp_add_jobserver
	@job_id = @jobId
	, @server_name = N''(local)''
;

' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'backup-compression-enabled'
	, 100
	, 'equals'
	, 'Config:backup compression default'
	, '1'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC sys.sp_configure
	N''backup compression default'', N''1''
' + 'GO
RECONFIGURE WITH OVERRIDE
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'clr-enabled'
	, 100
	, 'equals'
	, 'Config:clr enabled'
	, '0'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC sp_configure 
	''show advanced options'', 1;
' + 'GO
RECONFIGURE;
' + 'GO
sp_configure ''clr enabled'', 0
;
' + 'GO
RECONFIGURE;
' + 'GO
EXEC sp_configure 
	''show advanced options'', 0;
' + 'GO
RECONFIGURE;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'xp_cmdshell-disabled'
	, 100
	, 'equals'
	, 'Config:xp_cmdshell'
	, '0'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC sp_configure 
	''show advanced options'', 1;
' + 'GO
RECONFIGURE;
' + 'GO
sp_configure ''xp_cmdshell'', 0
;
' + 'GO
RECONFIGURE;
' + 'GO
EXEC sp_configure 
	''show advanced options'', 0;
' + 'GO
RECONFIGURE;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'database-mail-configured'
	, 1500
	, 'equals'
	, 'Database Mail Configured'
	, 'yes'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
;
' + 'GO
EXEC sp_configure 
	''show advanced options'', 1;
' + 'GO
RECONFIGURE;
' + 'GO
EXEC sp_configure 
	''Database Mail XPs'', 1;
' + 'GO
RECONFIGURE;
' + 'GO
EXEC sp_configure 
	''show advanced options'', 0;
' + 'GO
RECONFIGURE;
' + 'GO
'
	)
;

----------------------------------------------------------------------
-- add check for: database mail account assigned to profile
-- add check for: database operator
----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-email-profile-set'
	, 1600
	, 'notnull'
	, 'Agent E-mail Profile'
	, '%'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [msdb]
;
' + 'GO

EXEC msdb.dbo.sp_set_sqlagent_properties
	@email_save_in_sent_folder=1
;

EXEC master.dbo.xp_instance_regwrite
	N''HKEY_LOCAL_MACHINE''
	, N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent''
	, N''UseDatabaseMail''
	, N''REG_DWORD''
	, 1
;

EXEC master.dbo.xp_instance_regwrite
	N''HKEY_LOCAL_MACHINE''
	, N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent''
	, N''DatabaseMailProfile''
	, N''REG_SZ''
	, N''DBA_Profile''
;
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'server1-primary-node-check'
	, 900
	, 'equals'
	, 'PhysicalNetBIOS'
	, 'server1-active-node-name'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleExcludes (
	ruleId
	, systemExclude
	)
VALUES (
	@lastRuleId
	, 'SERVER1'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'sa-empty-password'
	, 100
	, 'equals'
	, 'SA has EMPTY password'
	, 'no'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
DECLARE @newpw VARCHAR(128);
SET @newpw = CAST(NEWID() AS VARCHAR(36))
	+ CAST(NEWID() AS VARCHAR(36))
	+ CAST(NEWID() AS VARCHAR(36))
	+ CAST(CAST(NEWID() AS VARCHAR(36)) AS VARCHAR(20))
;
DECLARE @SQL NVARCHAR(500);
SET @SQL = ''ALTER LOGIN sa WITH PASSWORD = '''''' 
	+ @newpw 
	+ '''''''';
EXECUTE sys.sp_executesql
	@stmt = @SQL
;
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'mb-dbmail-account'
	, 1530
	, 'contains'
	, 'Database Mail Accounts'
	, 'DBA_Account'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
;
' + 'GO
EXEC msdb.dbo.sysmail_add_account_sp
	@account_name = ''DBA_Account''
	, @description = ''DBA E-mail Notifications''
	, @email_address = ''dba@your-domain.com''
	, @display_name = ''DBA''
	, @replyto_address = ''dba@your-domain.com''
	, @mailserver_type = ''SMTP''
	, @mailserver_name = ''email.your-domain.com''
	, @port = 25
	, @use_default_credentials = 0
;
' + 'GO
'
	)
;

----------------------------------------------------------------------
/*
INSERT INTO auditRuleFix (
    ruleId
    , ruleSQL
    )
VALUES (
	@lastRuleId
    , 'USE [master]
;
' + 'GO
EXEC msdb.dbo.sysmail_add_profileaccount_sp
	@profile_name = ''DBA_Profile''
	, @account_name = ''DBA_Account''
	, @sequence_number = 1
;
' + 'GO
'
	)
;
*/
----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'mb-dbmail-profile'
	, 1540
	, 'contains'
	, 'Database Mail Profiles'
	, 'DBA_Profile'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
;
' + 'GO
EXEC msdb.dbo.sysmail_add_profile_sp
	@profile_name = ''DBA_Profile''
	, @description = ''Profile for DBAs''
;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'default-memory-not-limited'
	, 3000
	, 'notequals'
	, 'Config:max server memory (MB)'
	, '2147483647'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC sp_configure 
	''show advanced options'', 1;
' + 'GO
RECONFIGURE;
' + 'GO
DECLARE @ServerPhysicalMemory VARCHAR(40);
SET @ServerPhysicalMemory = 0;
DECLARE @param NVARCHAR(500);
DECLARE @SQL_Hardware NVARCHAR(2000);
DECLARE @ServerProductVersion VARCHAR(100);
SET @ServerProductVersion = CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR(100));
DECLARE @ServerProductVersionMajor INT;
SET @ServerProductVersionMajor = PARSENAME(@ServerProductVersion, 4);

SET @param = ''@physmem NVARCHAR(1000) OUTPUT'';
IF (CAST(@ServerProductVersionMajor AS INT) < 11)
BEGIN
	SET @SQL_Hardware = ''SELECT''
		+ '' @physmem = physical_memory_in_bytes/1048576''
		+ '' FROM sys.dm_os_sys_info''
	;
END;
ELSE
BEGIN
	SET @SQL_Hardware = ''SELECT''
		+ '' @physmem = physical_memory_kb/1024''
		+ '' FROM sys.dm_os_sys_info''
	;
END;

EXECUTE sp_executesql
	@stmt = @SQL_Hardware
	, @params = @param
	, @physmem = @ServerPhysicalMemory OUTPUT
;

DECLARE @SuggestedMaxMemory INT;
SET @SuggestedMaxMemory = CAST(@ServerPhysicalMemory AS INT) * .95;
PRINT ''Setting max server memory to '' + CAST(@SuggestedMaxMemory AS VARCHAR(10));
EXEC sp_configure 
	''max server memory'', @SuggestedMaxMemory;
' + 'GO
RECONFIGURE;
' + 'GO
EXEC sp_configure 
	''show advanced options'', 0;
' + 'GO
RECONFIGURE;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'mixed-mode-auth'
	, 2000
	, 'equals'
	, 'Authentication Mode'
	, 'mixed'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, '--
-- This change will require a restart of the server to take effect.
--
USE [master]
' + 'GO
EXEC master.dbo.xp_instance_regwrite
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
    , @value_name = ''LoginMode''
	, @type = REG_DWORD
    , @value = 2
;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'filestream-mode'
	, 2000
	, 'equals'
	, 'Config:filestream access level'
	, '2'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleIncludes (
	ruleId
	, systemInclude
	)
VALUES (
	@lastRuleId
	, 'SERVER2'
	)
	, (
	@lastRuleId
	, 'SERVER3'
	)
;

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'PRINT ''*** Filestream not configured correctly!  Please fix.''
;'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'maxdop-default'
	, 4000
	, 'equals'
	, 'Config:max degree of parallelism'
	, '0'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
' + 'GO
EXEC sp_configure 
	''show advanced options'', 1;
' + 'GO
RECONFIGURE;
' + 'GO
sp_configure ''max degree of parallelism'', 0
;
' + 'GO
RECONFIGURE;
' + 'GO
EXEC sp_configure 
	''show advanced options'', 0;
' + 'GO
RECONFIGURE;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'agent-category-servmaint'
	, 1800
	, 'notnull'
	, 'AgentCategory:Server Maintenance'
	, '%'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [msdb]
' + 'GO
EXEC dbo.sp_add_category
	@class = N''JOB''
	, @type = N''LOCAL''
	, @name = N''Server Maintenance''
;
' + 'GO
'
	)
;

----------------------------------------------------------------------
INSERT INTO auditRules (
	ruleName
	, runOrder
	, action
	, configKey
	, target
	)
VALUES (
	'sysoperator-mbdba'
	, 1550
	, 'notnull'
	, 'SysOperator:DBA'
	, '%'
	)
;

SET @lastRuleId = SCOPE_IDENTITY();

INSERT INTO auditRuleFix (
	ruleId
	, ruleSQL
	)
VALUES (
	@lastRuleId
	, 'USE [master]
;
' + 'GO
EXEC msdb.dbo.sp_add_operator
	@name=N''DBA''
	, @enabled=1
	, @pager_days=0
	, @email_address=N''dba@your-domain.com''
;
' + 'GO
'
	)
;

----------------------------------------------------------------------
-- End of Rules
--
-- When running daily comparisons, skip these:
--
CREATE TABLE auditCompareRules (
	compRulesId INT IDENTITY PRIMARY KEY
	, configKey VARCHAR(100) NOT NULL
	, action VARCHAR(100) NOT NULL
	)
;

INSERT INTO auditCompareRules (
	configKey
	, action
	)
VALUES (
	'SQL Server Uptime'
	, 'skip'
	)
	, (
	'Config:min server memory (MB)'
	, 'skip'
	)
	, (
	'Config:allow updates'
	, 'skip'
	)
	, (
	'Free Space GB Drive: %'
	, 'skip'
	)
	, (
	'Perf:%'
	, 'skip'
	)
;

--
-- EXEC dbo.checkRules;
--
-- SELECT *
-- FROM auditRules
-- ORDER BY runOrder;
--
-- SELECT ar.rulename
-- 	, ai.*
-- FROM auditRuleIncludes AS ai
-- 	JOIN auditRules AS ar
-- 		ON ar.ruleId = ai.ruleId
-- ORDER BY ruleId;
--
-- SELECT ar.rulename
-- 	, ae.*
-- FROM auditRuleExcludes AS ae
-- 	JOIN auditRules AS ar
-- 		ON ar.ruleId = ae.ruleId
-- ORDER BY ruleId;
--
-- SELECT ar.rulename
-- 	, arf.*
-- FROM auditRuleFix AS arf
-- 	JOIN auditRules AS ar
-- 		ON ar.ruleId = arf.ruleId
-- ORDER BY ruleId;
-- GO
--

GO
